package com.eascs.banknet.boc.dao;

import java.lang.reflect.InvocationTargetException;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.inject.Inject;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.eascs.app.dao.JdbcSession;
import com.eascs.banknet.boc.vo.boc001.BOC001Bean;
import com.eascs.banknet.boc.vo.boc001.BOC001Element;
import com.eascs.banknet.common.dao.BaseO2oDao;
import com.eascs.banknet.common.utils.IdcardUtils;
import com.eascs.banknet.constant.EAContant;
import com.eascs.banknet.data.dao.DditemDao;
import com.eascs.common.util.StringUtil;

@Repository
public class BOC001Dao extends BaseO2oDao{
	@Inject
	private JdbcSession crmJdbcSession;
	@Inject
	private DditemDao dditemDao;
	
	private Map<String, Object> cityMap = null;
	private Map<String, Object> provinceMap = null;
	private Map<String, Object> countyMap = null;
	
	//从o2o库获取贷款信息
	public BOC001Bean queryData(String startId){
		StringBuffer sql = new StringBuffer();
		sql.append("select t2.bank_applyno 'APPNO',")//银行申请单号
			.append(" t2.loanapply_no 'loanapplyNo',")//贷款单号
			.append(" t2.loan_id 'customerId', ")//客户ID
			.append(" IFNULL(t1.instrument_number,0)'PLAATRN_LAFAMANO',")//文书编号
			.append(" ROUND(t1.ea_mount, 2) 'PLAATRN_LAFDRAMT', ")//申请借款金额
			.append(" t1.loan_term 'PLAATRN_LAFTERM',")//申请借款期限
			.append(" t1.bank_account 'PLAATRN_RECEIVEDEBITNO',")//收款人借记卡卡号
			.append(" t1.bank_account 'PLAATRN_LAFPDEBITNO',")//还款人借记卡卡号
			.append(" ROUND(t1.loana_mount * 1.5,2) as 'RETAILTRAN_PRICE',")//资金需求总额
			.append(" ROUND(t1.loana_mount * 1.5 - t1.loana_mount, 2) as 'PLAATRN_LAFAPYAM',")//自有资金
			.append(" DATE_FORMAT(ADDDATE(t1.submit_date_credit, INTERVAL t1.loan_term MONTH),'%Y%m%d') as 'PLAATRN_CONDRMONDATE',")//合同提款截至日期
			.append(" case t1.repay_mentway WHEN 'RMT' THEN '2' WHEN 'AKT' THEN '4' else '3' end as 'PLAATRN_LAFRTMOD' ")//还款方式
			.append("   from o2o_loanapply t1, o2o_loan_user t2")
			.append("   where t1.loanapply_no = t2.loanapply_no  ")
			.append("   and t1.bank_type='BC' ")
			.append(" and t1.wf_type in ('S')")
			.append("   and t1.islock = 'N' and t1.bank_flg = 'N'")
			.append(" and contract_flag = 'Y'")
			//.append("   and t1.instrument_number is not null ")//贷款合同编号编号
			.append("   and t1.bank_account is not null")
			.append("   and t2.bank_status in ('01B3','0101','0102','0103') and t2.bank_applyno is not null")
			.append("   and t2.spouse_id is null ")
			.append("   and EXISTS (SELECT 1 FROM o2o_datalist t3 WHERE t3.loanapply_no = t1.loanapply_no AND t3.datalist_code = 'BANKAPPLYDATA' AND t3.is_delete = 'N') ");
			if(StringUtil.hasText(startId)){
				sql.append("   AND t2.bank_applyno > '").append(startId).append("' ");
			};
			sql.append(" ORDER BY t2.bank_applyno LIMIT 1");
		return  super.findEntity(sql.toString(), BOC001Bean.class);
		
	}
	
	//从crm库中查询客户和门店资产信息，并合并贷款信息
	public BOC001Element joinCrmInfo(BOC001Bean bean){
		BOC001Element element = new BOC001Element();
		StringBuffer sqlCrm = new StringBuffer();
		sqlCrm.append(" SELECT t1.customer_family_name 'CCTPERTRN_PERLSTNM',")//客户姓
			.append(" t1.customer_given_name 'CCTPERTRN_PERFSTNM',")//客户名
			.append(" t1.customer_name 'PLAATRN_RECEIVEPER',")//收款人
			.append(" t1.sex 'CCTPERTRN_CBISEXID',")//性别
			.append("t1.id_type 'CCTPERTRN_CBICERTY',")//证件类别
			.append(" t1.id_number 'CCTPERTRN_CBICERNO',")//证件号码
			.append(" t2.issuing_authority 'CCTPERTRN_PERCEROP',")//发证机关
			.append(" DATE_FORMAT(t2.license_expiration_date, '%Y%m%d') 'CCTPERTRN_PERCERDT',")//证件到期日
			.append(" t1.customer_name_pinyin 'CCTPERTRN_OTHERTEL',")//拼音
			.append(" case t1.marital_status when 1 then 0 when 2 then 1 when 3 then 6 else 7 end 'CCTPERTRN_PERMRTST',")//婚姻状况
			.append(" t2.home_phone 'CCTPERTRN_PERHMTEL', ")//住宅电话
			.append("t1.mobile_number 'CCTPERTRN_PERMBLNO',")//移动电话
			.append(" t2.recipient_address 'CCTPERTRN_PERHMAD1',SUBSTR(t1.id_number,7,8) as 'CCTPERTRN_CBIBTHDA',")//家庭住址(详细),出生日期
			.append(" t2.recipient_city 'city',")//居住城市
			.append(" t2.recipient_province 'province',")//居住省
			.append(" IFNULL(t2.recipient_county,'0') 'country',")//居住省
			.append(" t3.shopname 'CCTPERTRN_PERCMPNM',")//现工作单位
			.append(" t3.officeaddress 'CCTPERTRN_PERCMPAD', ")//工作单位地址1(详细)
			.append(" t3.officeprovince 'officeprovince',")//工作单位所在省区
			.append(" DATE_FORMAT(t3.stupdate, '%Y%m%d') 'CCTPERTRN_WKIBEDAT', ")//现工作日期
			//.append(" DATE_FORMAT(t3.stupdate, '%Y%m%d') 'CCTPERTRN_PERNLVDA' ,")//现住址时间
			//.append(" DATE_FORMAT(ADDDATE(t3.stupdate, INTERVAL -1 DAY), '%Y%m%d') 'CCTPERTRN_PERWOKDA', ")//参加工作日期
			.append(" t3.officecity 'officecity', IFNULL(t3.officecounty,'0') 'officecounty'")
			.append(" from customer_basic_info t1, customer_expansion_info t2, customer_shop_info t3")
			.append(" where t1.customer_id = t2.customer_id and t1.customer_id =t3.customer_id ")
			.append(" and t3.shoptype = 'MAN' and t1.customer_id = ?");
		BOC001Bean customerBean = crmJdbcSession.findEntity(sqlCrm.toString(), BOC001Bean.class, bean.getCustomerId());
		if(null == customerBean){
			return null;
		}
		try {
			BeanUtils.copyProperties(element, bean);
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		element.setCCTPERTRN_PERLSTNM(customerBean.getCCTPERTRN_PERLSTNM());//客户姓
		element.setCCTPERTRN_PERFSTNM(customerBean.getCCTPERTRN_PERFSTNM());//客户名
		element.setPLAATRN_RECEIVEPER(customerBean.getPLAATRN_RECEIVEPER());//收款人姓名
		element.setCCTPERTRN_CBISEXID(customerBean.getCCTPERTRN_CBISEXID());//性别
		element.setCCTPERTRN_CBICERTY(EAContant.IDTypeMap.get(customerBean.getCCTPERTRN_CBICERTY()));//证件类别
		element.setCCTPERTRN_CBICERNO(customerBean.getCCTPERTRN_CBICERNO());//证件号码
		element.setCCTPERTRN_PERCEROP(StringUtils.substring(customerBean.getCCTPERTRN_PERCEROP(), 0, 10));//发证机关
		element.setCCTPERTRN_PERCEROP(customerBean.getCCTPERTRN_PERCEROP());//发证机关
		element.setCCTPERTRN_PERCERDT(customerBean.getCCTPERTRN_PERCERDT());//证件到期日
		element.setCCTPERTRN_OTHERTEL(customerBean.getCCTPERTRN_OTHERTEL());//拼音/其他名称
		element.setCCTPERTRN_PERMRTST(customerBean.getCCTPERTRN_PERMRTST());//婚姻状况
		element.setCCTPERTRN_PERHMTEL(customerBean.getCCTPERTRN_PERHMTEL());//住宅电话
		element.setCCTPERTRN_PERMBLNO(customerBean.getCCTPERTRN_PERMBLNO());//移动电话
		element.setCCTPERTRN_PERHMAD1(customerBean.getCCTPERTRN_PERHMAD1());//家庭住址1 (详细)
		element.setCCTPERTRN_WKIBEDAT(customerBean.getCCTPERTRN_WKIBEDAT());//现工作日期
		if(null == cityMap){
			cityMap = dditemDao.getDdItemByCode("CITY");
		}
		if(null == provinceMap){
			provinceMap = dditemDao.getDdItemByCode("PROVINCE");
		}
		if(null == countyMap){
			countyMap = dditemDao.getDdItemByCode("COUNTY");
		}
		String city = customerBean.getCity();
		String province = customerBean.getProvince();
		String country = customerBean.getCountry();
		String officecity = customerBean.getOfficecity();
		String officeprovince = customerBean.getOfficeprovince();
		String officecounty = customerBean.getOfficecounty();
		String sex = customerBean.getCCTPERTRN_CBISEXID();
		if (!StringUtil.hasText(sex)) {
			String sexValue = IdcardUtils.getGenderByIdCard(customerBean.getCCTPERTRN_CBICERNO());
			element.setCCTPERTRN_CBISEXID(EAContant.sexMap.get(sexValue));// 性别
		} else {
			element.setCCTPERTRN_CBISEXID(EAContant.sexMap.get(sex));// 性别
		}
		//没有区号的时候区存市，省存区
		if(StringUtil.hasText(customerBean.getCountry()) && !StringUtils.equalsIgnoreCase(customerBean.getCountry(), "0")){
			element.setCCTPERTRN_PERHMAD2((String)countyMap.get(country));//家庭住址2(区/县)
			String str = provinceMap.get(province)+""+cityMap.get(city);
			element.setCCTPERTRN_PERHMAD3(str);//家庭住址3 (省/市)
		}else{
			element.setCCTPERTRN_PERHMAD2((String)cityMap.get(city));//家庭住址2(区/县)
			element.setCCTPERTRN_PERHMAD3((String)provinceMap.get(province));//家庭住址3 (省/市)
		}
		if(StringUtil.hasText(officecounty) && !StringUtils.equalsIgnoreCase(officecounty, "0")){
			element.setCCTPERTRN_PERCMPAD2((String)countyMap.get(officecounty));//工作单位地址2(区/县)
			String str = provinceMap.get(officeprovince)+""+cityMap.get(officecity);
			element.setCCTPERTRN_PERCMPAD3(StringUtils.substring(str,0,10));//工作单位地址3(省/市)
		}else{
			element.setCCTPERTRN_PERCMPAD2((String)cityMap.get(officecity));//工作单位地址2(区/县)
			element.setCCTPERTRN_PERCMPAD3((String)provinceMap.get(officeprovince));
		}
		String CCTPERTRN_DOMICILE = "";
		if ("4403".equals(city))// 深圳本地
		{
			CCTPERTRN_DOMICILE = "4";
		} else {
			if ("44".equals(province))//
			{
				CCTPERTRN_DOMICILE = "3";// 本省
			} else {
				CCTPERTRN_DOMICILE = "2";// 外省
			}
		}
		element.setCCTPERTRN_DOMICILE(CCTPERTRN_DOMICILE);//客户户籍
		element.setCCTPERTRN_CBIBTHDA(customerBean.getCCTPERTRN_CBIBTHDA());//出生日期
		element.setCCTPERTRN_PERCMPNM(customerBean.getCCTPERTRN_PERCMPNM());//现工作单位
		element.setCCTPERTRN_PERCMPAD(customerBean.getCCTPERTRN_PERCMPAD());//工作单位地址1(详细)
		return element;
	}
	
	public String checkElement(BOC001Element element){
		List<String> list = new LinkedList<>();
/*		if(StringUtil.isEmpty(element.getPLAATRN_LAFAMANO())){
			list.add("合同编号不能为空");
		}*/
		if(StringUtil.isEmpty(element.getPLAATRN_LAFPDEBITNO())){
			list.add("放款款/还款卡号不能为空");
		}
		if(StringUtil.isEmpty(element.getPLAATRN_RECEIVEPER())){
			list.add("收款人姓名不能为空");
		}
		if(StringUtil.isEmpty(element.getCCTPERTRN_PERCEROP())){
			list.add("发证机关不能为空");
		}
		if(StringUtil.isEmpty(element.getCCTPERTRN_PERCERDT())){
			list.add("证件到期日不能为空");
		}
		if(StringUtil.isEmpty(element.getCCTPERTRN_PERMBLNO())){
			list.add("移动电话不能为空");
		}
		if(StringUtil.isEmpty(element.getCCTPERTRN_PERHMAD1())){
			list.add("收件地址(详细)不能为空");
		}
		if(StringUtil.isEmpty(element.getCCTPERTRN_PERHMAD2())){
			list.add("收件地址(区/县)不能为空");
		}
		if(StringUtil.isEmpty(element.getCCTPERTRN_PERHMAD3())){
			list.add("收件地址(省/市)不能为空");
		}
		if(StringUtil.isEmpty(element.getCCTPERTRN_PERCMPNM())){
			list.add("门店名称(现工作单位)不能为空");
		}
		if(StringUtil.isEmpty(element.getCCTPERTRN_PERCMPAD())){
			list.add("门店办公地址(详细)不能为空");
		}
		if(StringUtil.isEmpty(element.getCCTPERTRN_PERCMPAD2())){
			list.add("门店办公地址(区/县)不能为空");
		}
		if(StringUtil.isEmpty(element.getCCTPERTRN_PERCMPAD3())){
			list.add("门店办公地址(省/市)不能为空");
		}
		if(StringUtil.isEmpty(element.getCCTPERTRN_WKIBEDAT())){
			list.add("门店成立日期(现工作日期)不能为空");
		}
		return StringUtils.join(list,",");
	}

	@Transactional("o2oTransactionManager")
	public void updateInfo(String loanapplyNo, String appno) throws Exception{
		String sql = "UPDATE o2o_loanapply SET bank_flg ='Y' WHERE loanapply_no=? ";
		int rw1 = super.execute(sql, loanapplyNo);
		if(rw1 <= 0 ){
			throw new RuntimeException("贷款申请完成，更新贷款状态失败，loanapplyNo:"+loanapplyNo);
		}
		String sql2 = "UPDATE o2o_loan_user SET bank_status='2' WHERE bank_applyno=? ";
		int rw2 = super.execute(sql2, appno);
		if(rw2 <= 0 ){
			throw new RuntimeException("贷款申请完成，更新银行状态失败，loanapplyNo:"+loanapplyNo);
		}
	}
}
